/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package BL;

import Bean.Truong;
import Bean.TuyenSinh;
import Util.DBConnection;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author AD_DA
 */
public class BLTruong {
    public List<Truong> getDSTruong(int MaTinh) throws SQLException{
        Connection conn=null;
        conn=DBConnection.getConnection();
        List<Truong> listTruong=new ArrayList<Truong>();
        String sql="SELECT MATRUONG, MATINH, TENTRUONG, DIACHITRUONG, SODIENTHOAITRUONG, EMAILTRUONG, "
                + "WEBTRUONG, HINHTRUONG, LOGOTRUONG, GIOITHIEUTRUONG FROM truong WHERE MATINH="+MaTinh;
        if(conn!=null){
            Statement stm=(Statement) conn.createStatement();
            ResultSet rs=stm.executeQuery(sql);
            while(rs.next()){
                Truong tr=new Truong();
                tr.setMaTruong(rs.getInt("MATRUONG"));
                tr.setMaTinh(rs.getInt("MATINH"));
                tr.setTenTruong(rs.getString("TENTRUONG"));
                tr.setDiaChi(rs.getString("DIACHITRUONG"));
                tr.setEmail(rs.getString("EMAILTRUONG"));
                tr.setSoDT(rs.getString("SODIENTHOAITRUONG"));
                tr.setGioiThieu(rs.getString("GIOITHIEUTRUONG"));
                tr.setWebTruong(rs.getString("WEBTRUONG"));
                tr.setHinhAnh(rs.getString("HINHTRUONG"));
                tr.setLogo(rs.getString("LOGOTRUONG"));
                listTruong.add(tr);
            }
            conn.close();
        }
        return listTruong;
    }
    public Truong getTruong(int MaTruong) throws SQLException{
        Connection conn=null;
        conn=DBConnection.getConnection();
        Truong tr=new Truong();
        String sql="SELECT MATRUONG, MATINH, TENTRUONG, DIACHITRUONG, SODIENTHOAITRUONG, EMAILTRUONG, "
                + "WEBTRUONG, HINHTRUONG, LOGOTRUONG, GIOITHIEUTRUONG FROM truong WHERE MATRUONG="+MaTruong;
        if(conn!=null){
            Statement stm=(Statement) conn.createStatement();
            ResultSet rs=stm.executeQuery(sql);
            if(rs.next()){
                tr.setMaTruong(rs.getInt("MATRUONG"));
                tr.setMaTinh(rs.getInt("MATINH"));
                tr.setTenTruong(rs.getString("TENTRUONG"));
                tr.setDiaChi(rs.getString("DIACHITRUONG"));
                tr.setEmail(rs.getString("EMAILTRUONG"));
                tr.setSoDT(rs.getString("SODIENTHOAITRUONG"));
                tr.setGioiThieu(rs.getString("GIOITHIEUTRUONG"));
                tr.setWebTruong(rs.getString("WEBTRUONG"));
                //tr.setHinhAnh(rs.getString("HINHTRUONG"));
                //tr.setLogo(rs.getString("LOGOTRUONG"));
            }
            
        }
        conn.close();
        return tr;
    }
    public int InsertTruong(int MATINH,String TENTRUONG,String DIACHITRUONG,String SODIENTHOAITRUONG,String EMAILTRUONG,String WEBTRUONG,String HINHTRUONG,String LOGOTRUONG,String GIOITHIEUTRUONG) throws SQLException{
        Connection conn=null;
        conn=DBConnection.getConnection();
        int rs=0;
        String sql="INSERT INTO truong(MATRUONG, MATINH, TENTRUONG, DIACHITRUONG, SODIENTHOAITRUONG, EMAILTRUONG, WEBTRUONG, "
                + "HINHTRUONG, LOGOTRUONG, GIOITHIEUTRUONG) VALUES (?,?,?,?,?,?,?,?,?,?)";
        String sql1="SELECT MAX( matruong ) max_T FROM truong";
        if(conn!=null){
            Statement stm=(Statement) conn.createStatement();
            ResultSet rs1=stm.executeQuery(sql1);
            int max=0;
            if(rs1.next()) max=rs1.getInt("max_T");
            PreparedStatement preStm=(PreparedStatement) conn.prepareStatement(sql);
            preStm.setInt(1,max+1 );
            preStm.setInt(2,MATINH);
            preStm.setString(3,TENTRUONG );
            preStm.setString(4,DIACHITRUONG );
            preStm.setString(5,SODIENTHOAITRUONG );
            preStm.setString(6,EMAILTRUONG );
            preStm.setString(7,WEBTRUONG );
            preStm.setString(8,HINHTRUONG );
            preStm.setString(9,LOGOTRUONG );
            preStm.setString(10,GIOITHIEUTRUONG );
            rs=preStm.executeUpdate(sql);
        }
        conn.close();
        return rs;
    }
    public int UpdateTruong(int MATRUONG,int MATINH,String TENTRUONG,String DIACHITRUONG,String SODIENTHOAITRUONG,String EMAILTRUONG,String WEBTRUONG,String HINHTRUONG,String LOGOTRUONG,String GIOITHIEUTRUONG) throws SQLException
    {
        Connection conn=null;
        conn=DBConnection.getConnection();
        int rs=0;
        String sql="UPDATE truong SET MATINH=?, TENTRUONG=?, DIACHITRUONG=?, SODIENTHOAITRUONG=?, "
                + "EMAILTRUONG=?, WEBTRUONG=?, HINHTRUONG=?, LOGOTRUONG=?, GIOITHIEUTRUONG=? WHERE MATRUONG=?";
        if(conn!=null){
            PreparedStatement preStm=(PreparedStatement) conn.prepareStatement(sql);
            preStm.setInt(1,MATINH);
            preStm.setString(2,TENTRUONG );
            preStm.setString(3,DIACHITRUONG );
            preStm.setString(4,SODIENTHOAITRUONG );
            preStm.setString(5,EMAILTRUONG );
            preStm.setString(6,WEBTRUONG );
            preStm.setString(7,HINHTRUONG );
            preStm.setString(8,LOGOTRUONG );
            preStm.setString(9,GIOITHIEUTRUONG );
            preStm.setInt(10,MATRUONG );
            rs=preStm.executeUpdate(sql);
        }
        conn.close();
        return rs;
    }
    public List<TuyenSinh> getDSDiemChuan(int MaNganh, int MaNV) throws SQLException{
        Connection conn=null;
        conn=DBConnection.getConnection();
        List<TuyenSinh> listTS=new ArrayList<TuyenSinh>();
        String sql="SELECT T.MANGANH,Ng.TENNGANH T.NAM, N.MANV, N.TENNV, K.MAKHOI, K.TENKHOI, T.SOLUONGTS, T.DIEMCHUAN, T.NGAYTS "
                + "FROM tuyensinh T, nguyenvong N, nganh Ng, khoi K "
                + "WHERE T.MANGANH=Ng.MANGANH AND T.MAKHOI=K.MAKHOI AND T.MANV=N.MANV AND T.MANGANH="+MaNganh+" AND N.MANV="+MaNV+";";
        if(conn!=null){
            Statement stm=(Statement) conn.createStatement();
            ResultSet rs=stm.executeQuery(sql);
            while(rs.next()){
                TuyenSinh ts=new TuyenSinh();
                ts.setMaNganh(rs.getInt("MANGANH"));
                ts.setMaKhoi(rs.getInt("MAKHOI"));
                ts.setMaNV(rs.getInt("MANV"));
                ts.setTenKhoi(rs.getString("TENKHOI"));
                ts.setTenNV(rs.getString("TENNV"));
                ts.setNam(rs.getInt("NAM"));
                ts.setNgayTS(rs.getDate("NGAYTS"));
                ts.setDiemChuan(rs.getFloat("DIEMCHUAN"));
                ts.setSL(rs.getInt("SOLUONGTS"));
                ts.setTenNganh(rs.getString("TENNGANH"));
                listTS.add(ts);
            }
        }
        conn.close();
        return listTS;
    }
//    public List<String> getDSMaNV(int MaTruong){
//        String sql="SELECT DISTINCT(N.MANV), N.TENNV FROM tuyensinh T, nguyenvong N, truong Tr, khoa K, nganh Ng "
//                + "WHERE T.MANGANH=Ng.MANGANH AND Ng.MAKHOA=K.MAKHOA AND K.MATRUONG=Tr.MATRUONG AND T.MANV=N.MANV";
//    }
}
